
Alter proc sp_create_update_work_order
(

	@Item_Number		varchar(50),
	@Item_Desc		varchar(500),
	@Work_Order_No		varchar(50),
	@Work_Order_Id		int,
	@Qty_Completed		int,
	@Qty_Rejected		int,
	@Qty_Open		int,
	@Ord_Date		datetime,
	@Rel_Date		datetime,
	@Due_Date		datetime,
	@Wo_Status		varchar(50),
	@Adaptor_Date		varchar(50)
)

as

begin
	

	declare @process_step_id int
	declare @lot_id int
	declare @lot_present int 

	insert into QAD_Table( Item_Number, Item_Desc, Work_Order_No, Work_Order_Id, Qty_Completed, Qty_Rejected,
 	Qty_Open, Ord_Date, Rel_Date, Due_Date, Wo_Status, Adaptor_Date, Time_Stamp ) 
	values ( @Item_Number, @Item_Desc, @Work_Order_No, @Work_Order_Id, @Qty_Completed, @Qty_Rejected,
 	@Qty_Open, @Ord_Date, @Rel_Date, @Due_Date, @Wo_Status, @Adaptor_Date, Current_timestamp )


	insert into QAD_History_Table( Item_Number, Item_Desc, Work_Order_No, Work_Order_Id, Qty_Completed, Qty_Rejected,
 	Qty_Open, Ord_Date, Rel_Date, Due_Date, Wo_Status, Adaptor_Date, Time_Stamp ) 
	values ( @Item_Number, @Item_Desc, @Work_Order_No, @Work_Order_Id, @Qty_Completed, @Qty_Rejected,
 	@Qty_Open, @Ord_Date, @Rel_Date, @Due_Date, @Wo_Status, @Adaptor_Date, Current_timestamp )

	
	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number like @Item_Number  and PS_Type_Name like 'Assembly' and product.ProcessStep_ID=Process_Step.ProcessStep_ID 

	select @lot_id = lot_id from lot_table where lot_number = @Work_Order_No and processStep_id = @process_step_id

	if @lot_id > 0
	begin

		select @lot_present = count(*) from Work_Order_Lot_table where lot_id = @lot_id		

		if ( @lot_present > 0 )
		begin
		
			update Work_Order_Lot_table set Qty_Out = @Qty_Completed, Qty_Rejected = @Qty_Rejected, Qty_In = @Qty_Open, Issue_Date = @Ord_Date, Release_Date = @Rel_Date, Date_Out = @Due_Date, Status = @Wo_Status, Time_Stamp = Current_timestamp where Lot_ID = @lot_id

			update lot_table set lot_current_status = @Wo_Status where Lot_ID = @lot_id
	
		end
		else
		begin
	
			insert into Work_Order_Lot_table ( Lot_ID, Qty_Out, Qty_Rejected, Qty_In, Issue_Date, Release_Date, Date_Out, Status, Time_Stamp )
			values ( @lot_id,  @Qty_Completed, @Qty_Rejected, @Qty_Open, @Ord_Date, @Rel_Date, @Due_Date, @Wo_Status, Current_timestamp)

			update lot_table set lot_current_status = @Wo_Status where Lot_ID = @lot_id
		end

	end
end

